﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class LtrDAL:ILtrDAL
    {
        public int Add(LtrInfo jh)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, jh.Num),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, jh.Ku),
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jh.Jday),
                DALHelper.MakeInParam(":yl", OracleType.Number, jh.Yl),
                DALHelper.MakeInParam(":xday", OracleType.VarChar, jh.Xday)
            };
            return (DALHelper.RunCommand(@"insert into dd.LTR (num, ku, jday, yl, xday) values (:num, :ku, to_date(:jday,'yyyy-mm-dd'), :yl, to_date(:xday,'yyyy-mm-dd'))", prams));
        }
        public int Update(string num, decimal yl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":yl", OracleType.Number, yl)
            };
            return (DALHelper.RunCommand(@"update dd.LTR set yl = :yl where num = :num", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.LTR where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.LTR where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectByDay(string xday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":xday", OracleType.VarChar, xday)
            };

            return (DALHelper.RunProcReturn(@"select * from dd.LTR where xday = to_date(:xday,'yyyy-mm-dd')", prams, "Sbyday"));
        
        }
        public string AUTOID(string ku)
        {
            string Ntime = DateTime.Now.ToString();
            string kudm = "";
            switch (ku)
            {
                case "嘉东库":
                    kudm = "JD"; break;
                case "板材库":
                    kudm = "BC"; break;
                case "不锈钢热轧库":
                    kudm = "BXR"; break;
                case "不锈钢冷轧库":
                    kudm = "BXL"; break;
                case "碳钢热轧库":
                    kudm = "TR"; break;
                case "碳钢冷轧库":
                    kudm = "TL"; break;
                case "一高线":
                    kudm = "1G"; break;
                case "二高线":
                    kudm = "2G"; break;
                case "大棒线":
                    kudm = "DB"; break;
                case "四轧库":
                    kudm = "SZ"; break;
                case "酸洗库":
                    kudm = "SX"; break;
                case "化产库":
                    kudm = "HC"; break;
                default:
                    break;
            }
            return "LTR" + kudm + Ntime;
        }
        public string JHS(string ku, string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select count(distinct(jday)) from dd.LTR where ku = :ku and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public string YJHL(string ku, string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(yl) from dd.ltr where ku = :ku and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public string ZJHL(string ku, string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wcl + jk) from dd.lweekplan where ku = :ku and sday = to_date(:bt,'yyyy-mm-dd') and eday = to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public DataSet JHRQ(string ku, string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            return (DALHelper.RunProcReturn(@"select distinct(jday) from dd.ltr where ku = :ku and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams, "rqcx"));
        }
        public DataSet JHRQFW(string ku, string jday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            return (DALHelper.RunProcReturn(@"select sday, eday from dd.lweekplan where ku = :ku and sday <= to_date(:jday,'yyyy-mm-dd') and eday >= to_date(:jday,'yyyy-mm-dd')", prams, "kjrq"));
        }
        public string KURJH(string ku, string jday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select yl from dd.ltr where ku = :ku and jday = to_date(:jday,'yyyy-mm-dd')", prams);

            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        
        public string XCYJHTS(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select count(distinct(jday)) from dd.LTR where ku = '嘉东线材' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public DataSet XCRQFW(string jday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jday)
            };
            return (DALHelper.RunProcReturn(@"select sday, eday from dd.lweekplan where ku = '嘉东线材' and sday <= to_date(:jday,'yyyy-mm-dd') and eday >= to_date(:jday,'yyyy-mm-dd')", prams, "kjrq"));

        }
        public DataSet XCJHRQ(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select distinct(jday) from dd.LTR where ku = '嘉东线材' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams, "rqcx"));

        }
        public string XCYJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(yl) from dd.LTR where ku = '嘉东线材' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public string XCZJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select (wcl + jk) from dd.lweekplan where ku = '嘉东线材' and sday = to_date(:bt,'yyyy-mm-dd') and eday = to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        
        public string BCYJHTS(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select count(distinct(jday)) from dd.LTR where ku = '嘉东螺纹' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public DataSet BCRQFW(string jday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jday)
            };
            return (DALHelper.RunProcReturn(@"select sday, eday from dd.lweekplan where ku = '嘉东螺纹' and sday <= to_date(:jday,'yyyy-mm-dd') and eday >= to_date(:jday,'yyyy-mm-dd')", prams, "kjrq"));

        }
        public DataSet BCJHRQ(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select distinct(jday) from dd.LTR where ku = '嘉东螺纹' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams, "rqcx"));

        }
        public string BCYJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(yl) from dd.LTR where ku = '嘉东螺纹' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public string BCZJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wcl + jk) from dd.lweekplan where ku = '嘉东螺纹' and sday = to_date(:bt,'yyyy-mm-dd') and eday = to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        
        public string ZBYJHTS(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select count(distinct(jday)) from dd.LTR where ku = '嘉东板材' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public DataSet ZBRQFW(string jday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jday)
            };
            return (DALHelper.RunProcReturn(@"select sday, eday from dd.lweekplan where ku = '嘉东板材' and sday <= to_date(:jday,'yyyy-mm-dd') and eday >= to_date(:jday,'yyyy-mm-dd')", prams, "kjrq"));

        }
        public DataSet ZBJHRQ(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select distinct(jday) from dd.LTR where ku = '嘉东板材' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams, "rqcx"));

        }
        public string ZBYJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(yl) from dd.LTR where ku = '嘉东板材' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public string ZBZJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wcl + jk) from dd.lweekplan where ku = '嘉东板材' and sday = to_date(:bt,'yyyy-mm-dd') and eday = to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        
        public string JBYJHTS(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select count(distinct(jday)) from dd.LTR where ku = '嘉东卷板' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public DataSet JBRQFW(string jday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jday)
            };
            return (DALHelper.RunProcReturn(@"select sday, eday from dd.lweekplan where ku = '嘉东卷板' and sday <= to_date(:jday,'yyyy-mm-dd') and eday >= to_date(:jday,'yyyy-mm-dd')", prams, "kjrq"));

        }
        public DataSet JBJHRQ(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select distinct(jday) from dd.LTR where ku = '嘉东卷板' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams, "rqcx"));

        }
        public string JBYJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(yl) from dd.LTR where ku = '嘉东卷板' and jday >= to_date(:bt,'yyyy-mm-dd') and jday <= to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public string JBZJH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wcl + jk) from dd.lweekplan where ku = '嘉东卷板' and sday = to_date(:bt,'yyyy-mm-dd') and eday = to_date(:et,'yyyy-mm-dd')", prams);
            return obj.ToString();
        }
        public string JDJH(string cday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":jday", OracleType.VarChar, cday)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(yl) from dd.ltr where ku in ('嘉东线材', '嘉东螺纹', '嘉东卷板', '嘉东板材') and jday = to_date(:jday,'yyyy-mm-dd')", prams);

            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
    }
}
